﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Web;
using System.Data.OleDb;
using System.Data;
/*
 * Class Name : DAOBASE
 * Author : HÀ NGỌC THÀNH
 * Description :Truy vấn CSDL Accsess 
 * Release Date :14-12-2010
 * Project : Search Engine
 */
namespace DAL
{
    public class DAOBASE
    {
        private OleDbConnection con;
        private OleDbDataAdapter da;
        private OleDbCommand cmd;
        private OleDbDataReader rd;
        private OleDbTransaction tran;
        public DAOBASE()
        {
        }
        public void Open()
        {
            try
            {
                string _Provider = ConfigurationSettings.AppSettings["Provider"].ToString();
                string _Db = ConfigurationSettings.AppSettings["Data Source"].ToString();
                string _user = ConfigurationSettings.AppSettings["username"].ToString();
                string _pass = ConfigurationSettings.AppSettings["password"].ToString();
                bool _Authencation = Convert.ToBoolean(ConfigurationSettings.AppSettings["Authentication"].ToString());
                this.con = new OleDbConnection(CreateConnectionString(_Provider,_Db,_user,_pass,_Authencation));
                if (con.State == System.Data.ConnectionState.Closed || con.State == System.Data.ConnectionState.Broken)
                {
                    this.con.Open();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /*
         * Method :BeginTranSacTion
         * Descripttion : Begin a transaction
         */
        /// <summary>
        /// Bắt đầu 1 transaction
        /// </summary>
        public void BeginTranSacTion()
        {
            this.tran = this.con.BeginTransaction();
        }
        /*
         * Method :Rollback
         * Description : RollBack a transaction
         */
        /// <summary>
        /// RollBack a transaction
        /// </summary>
        public void RollBack()
        {
            this.tran.Rollback();
        }
        /*
         * Method : Commit 
         * Description : Hoàn thành 1 tran 
         */
        public void Commit()
        {
            this.tran.Commit();
        }
        /*
         * Method : Close 
         * Description : Đóng kết nối
         */
        public void Close()
        {
            this.con.Close();
        }
        public DataTable ExcuteQuery(String sql)
        {
            DataTable dt = new DataTable();
            try
            {
                this.Open();
                this.da = new OleDbDataAdapter(sql, this.con);
                da.Fill(dt);
            }
            catch { return null; }
            return dt;
        }
        /*
         * Method:ExcuteNonQuery
         * Description : Thực hiện các câu truy vấn dạng INSERT,UPDATE,DELETE
         * Param :
         *  SQL: Câu truy vấn SQL
         */
        public int ExcuteNonQuery(String sql)
        {
            int resurl = -1;
            try
            {
                this.cmd = new OleDbCommand();
                this.cmd.CommandType = CommandType.Text;
                this.cmd.CommandText = sql;
                this.cmd.CommandTimeout = 90;
                this.cmd.Connection = this.con;
                resurl = this.cmd.ExecuteNonQuery();
            }
            catch { return -1; }
            return resurl;
        }
        /*
         * Method:ExcuteNonQuery
         * Description : Thực hiện các câu truy vấn dạng INSERT,UPDATE,DELETE
         * Param :
         *  Obj : Đối tượng sẽ truy vấn
         *  Table Name : Tên bảng ser thực hiện
         *  flag : Hình thức thực hiện 
         *      1:INSERT
         *      2:UPDATE
         *      3:DELETE
         *   primary key: Khóa chính của bảng
         *  
         */
        public int ExcuteNonQuery(Object obj,String tableName,int flag,String []Primarykey)
        {
            int resurl = -1;
            try
            {
                this.cmd = new OleDbCommand();
                this.cmd.CommandType = CommandType.Text;
                this.cmd.CommandText = CreateQuery(tableName, obj, flag, Primarykey);
                this.cmd.CommandTimeout = 90;
                this.cmd.Connection = this.con;
                AddParameterWithValue(obj);
                resurl = this.cmd.ExecuteNonQuery();
            }
            catch { return -1; }
            finally { this.Close(); }
            return resurl;
        }
        /*
         * Method :CreateQuery
         * Description: tạo 1 câu truy vấn
         * Param: 
         *  1: INSERT
         *  2: Update
         *  3: Delete
         *  4: Select

         */
        public String CreateQuery(String tableName, object obj,int flag,String[] Primarykey)
        {
            String sql = "";
            switch(flag)
            {
                    //INSERT
                case 1:
                    {
                        String INSERT = "INSERT INTO " + tableName + "(";
                        sql = AppentSQL(INSERT,obj,1,Primarykey);
                        break;
                    }
                    //UPDATE
                case 2:
                    {
                        String UPDATE = "UPDATE "+tableName +" SET ";
                        sql = AppentSQL(UPDATE, obj, 2, Primarykey);
                        break;
                    }
                case 3:
                    {
                        String DELETE = "DELETE " + tableName;
                        sql = AppentSQL(DELETE, obj, 3, Primarykey);
                        break;
                    }
                case 4:
                    {
                        sql = "Select * from " + tableName;
                        break;
                    }

            }
            return sql;
        }
        /*
         * Method : AppentSQL
         * Description : Hàm sinh Prefext câu truy vấn có sử dụng prameter
         * Param : 
         *  SQL : các tiết đầu của câu truy vấn 
         *  obj : Đối tượng cần tạo truy vấn
         *  flag : Cờ yêu cầu sử lý
         *      1: INSERT
         *      2: UPDATE
         *      3: DELETE
         *   primary key : Khóa chính của bảng
         * 
         */
        public String AppentSQL(String sql,object obj,int flag,String [] Primarykey)
        { 
            String [] Name=ConvertClassToArray.GetParamName(obj);
            String Parameter = "Values (";
            String Condition = "Where ";
            for (int i = 0; i < Name.Length; i++)
            {
                //khi chưa đến cuối cùng
                if (flag == 1)
                {
                    if (i < Name.Length - 1)
                    {
                        sql += Name[i] + ",";
                        Parameter += "?,";
                    }
                    //da la tên cuối cùng
                    if (i == Name.Length - 1)
                    {
                        sql += Name[i] + ")";
                        Parameter += "? )";
                    }
                }
                //UPDATE
                if (flag == 2)
                {
                    if (i < Name.Length - 1)
                    {
                        sql += Name[i] + "=?,";
                    }
                    if (i == Name.Length - 1)
                    {
                        sql += Name[i] + "=?";
                        //lap de lay dieu kien

                    }
                    for (int j = 0; j < Primarykey.Length; j++)
                    {
                        //compare chuoi ten so voi name
                        if (Primarykey[j].Equals(Name[i]))
                        {
                            if (j < Primarykey.Length - 1)
                            {
                                Condition += Name[i] + "= ? and";
                            }
                            if (j == Primarykey.Length - 1)
                            {
                                Condition += Name[i] + "= ?";
                            }
                        }
                    }
                }
                //DELETE
                if (flag == 3)
                {
                    for (int j = 0; j < Primarykey.Length; j++)
                    {
                        //compare chuoi ten so voi name
                        if (Primarykey[j].Equals(Name[i]))
                        {
                            if (j < Primarykey.Length - 1)
                            {
                                Condition += Name[i] + "= ? and";
                            }
                            if (j == Primarykey.Length - 1)
                            {
                                Condition += Name[i] + "= ?";
                            }
                        }
                    }
                }
            }
            if (flag == 1)
            {
                sql += sql + Parameter;
            }
            if (flag == 2 && flag==3)
            {
                sql += sql + Condition;
            }
            return sql;
        }
        /*
         * Method :AddParameterWithValue
         * Desription : Tạo pramater cùng với dữ liệu
         * Param :obj tên đổi tượng có chứa dữ liệu
         * 
         */
        public void AddParameterWithValue(object obj)
        {
            try
            {
                String[] name = ConvertClassToArray.GetParamName(obj);
                Object[] value = ConvertClassToArray.GetValues(obj);
                for (int i = 0; i < name.Length; i++)
                {
                    this.cmd.Parameters.AddWithValue(name[i], value[i]);
                }
            }
            catch { return; }
        }
        /*
         * 
        * Method : AutoGenClauseCondition
        * Description : Hàm sinh cau truy van su cho tong cac du lieu tim thay
        * Param : 
        *  dstb : tong hop cac bang tim thay
        * 
        */
        public String AutoGenClauseCondition(DataTable[] dstb)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("Where ");
            String newstring;
            try
            {
                for (int i = 0; i < dstb.Length; i++)
                {
                    DataTable dt = new DataTable();
                    dt = dstb[i];
                    String[] s = new String[dt.Columns.Count];
                    s = getColumnName(dt);
                    int k = 0;
                    //bat dau 1 vong l?p b?ng con 
                    if (dt.Rows.Count > 0)
                    {
                        sb.Append("(");
                    }
                    foreach (DataRow dr in dt.Rows)
                    {
                        if (k < dt.Rows.Count)
                        {
                            //get name comlum
                            for (int m = 0; m < s.Length; m++)
                            {
                                if (k == dt.Rows.Count - 1)
                                {
                                    sb.Append(GetTableName(dt) + "." + s[m].ToString() + " = " + dr[s[m].ToString()].ToString() + " ");
                                }
                                else
                                {
                                    sb.Append(GetTableName(dt) + "." + s[m].ToString() + " = " + dr[s[m].ToString()].ToString() + " or ");
                                }
                            }
                        }
                        if (k == dt.Rows.Count)
                        {
                            //get name comlum
                            for (int m = 0; m < s.Length; m++)
                            {
                                if (k == dt.Rows.Count && i == dstb.Length - 1)
                                {
                                    sb.Append(GetTableName(dt) + "." + s[m].ToString() + " = " + dr[s[m].ToString()].ToString());
                                }
                                else
                                {
                                    sb.Append(GetTableName(dt) + "." + s[m].ToString() + " = " + dr[s[m].ToString()].ToString() + " or ");
                                }

                            }
                        }
                        k++;
                    }
                    if (dt.Rows.Count > 0)
                    {
                        if (i == dstb.Length - 2)
                        {
                            sb.Append(") and ");
                        }
                        else
                        {
                            sb.Append(") and ");
                        }

                    }
                }
                //su ly remove and cuoi cung 
                int index = sb.ToString().IndexOf("and", sb.ToString().Length - 4);
                if (index != -1)
                {
                    newstring = sb.ToString().Substring(0, index);
                }
                else
                {
                    newstring = sb.ToString();
                }
            }
            catch { return null; }
            return newstring;
        }
        public String AutoGenClauseConditionKHOA2(DataTable[] dstb)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("Where ");
            String newstring;
            try
            {
                int kt_and;
                for (int i = 0; i < dstb.Length; i++)
                {
                    kt_and = 0;
                    DataTable dt = new DataTable();
                    dt = dstb[i];
                    if (dt.Rows.Count==0||dt.Rows.Count > 100)
                    {
                        continue;
                        
                    }
                    kt_and = 1;
                    String[] s = new String[dt.Columns.Count];
                    s = getColumnName(dt);
                    DataRow dr=dt.Rows[0];
                    sb.Append("(");
                    for (int m = 0; m < dt.Rows.Count-1; m++)
                    {
                        dr = dt.Rows[m];
                        sb.Append(dt.TableName + "." + dt.Columns[0].ColumnName + " = " + dr[0].ToString() + " or ");
                        newstring = sb.ToString();
                    }               
                    sb.Append(dt.TableName + "." + dt.Columns[0].ColumnName + " = " + dt.Rows[dt.Rows.Count-1][0].ToString());
                    sb.Append(")");
                    if (i!=dstb.Length-1 && kt_and == 1)
                       sb.Append(" and ");
                }
          
            //su ly remove and cuoi cung 
            int index = sb.ToString().IndexOf("and", sb.ToString().Length - 4);
           
            if (index != -1)
            {
                newstring = sb.ToString().Substring(0, index);
            }
            else
            {
                newstring = sb.ToString();
            }
            }
            catch { return null; }
            return newstring;
        }
        /*
        * Method : getColumnName
        * Description : lay danh sach  ten comlum
        * Param : 
        *  dt : bang can tim kiem te
        * 
        */
        public String[] getColumnName(DataTable dt)
        {
            String[] s = new String[dt.Columns.Count];
            try
            {
               
                int i = 0;
                foreach (DataColumn dr in dt.Columns)
                {
                    s[i] = dr.ColumnName.ToString();
                    i++;
                }
            }
            catch { return null; }
            return s;
        }
        public String GetTableName(DataTable dt)
        {
            return dt.TableName;
        }
        /*
         * Method:ExcuteScalar
         * Description :Hàm thực hiện trả về kết quả là int có thể là count ,...
         */
        public int ExcuteScalar(String sql)
        {
            int resuft = -1;
            try
            {
                this.Open();
                this.cmd = new OleDbCommand();
                this.cmd.CommandType = CommandType.Text;
                this.cmd.CommandText = sql;
                this.cmd.CommandTimeout = 90;
                this.cmd.Connection = this.con;
                resuft =Convert.ToInt32( this.cmd.ExecuteScalar().ToString());
            }
            catch { return -1; }
            return resuft;
        }
        /*
        * Method:CreateConnectionString
        * Description :Hàm tạo chuỗi kết nối
        */
        public String CreateConnectionString(string pProvider, String pDatabase, String pUser, String pPassword, bool Authencation)
        {
            if (String.IsNullOrEmpty(pUser) && String.IsNullOrEmpty(pPassword) && !Authencation)
                return "Provider=" + pProvider + ";Data Source=" + System.Web.HttpContext.Current.Server.MapPath("" + pDatabase + "") + ";Persist Security Info=" + Authencation + ";";
            else
                return "Provider=" + pProvider + ";Data Source=" + System.Web.HttpContext.Current.Server.MapPath("" + pDatabase + "") + ";Jet OLEDB:Database Password=" + pPassword + ";";

        }
        /*
         * Method:GetClassName
         * Description:Lấy tên của đối tượng
         */
        public String GetClassName(object obj)
        {
            return obj.GetType().Name;
        }

        public String AutoGenClauseConditionKHOA(DataTable[] dstb)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("Where ");
            String newstring;
            try
            {
                for (int i = 0; i < dstb.Length; i++)
                {
                   
                    DataTable dt = new DataTable();
                    dt = dstb[i];
                    if (dt.Rows.Count > 100)
                        continue;
                    String[] s = new String[dt.Columns.Count];
                    s = getColumnName(dt);
                    int k = 0;
                    foreach (DataRow dr in dt.Rows)
                    {
                        if (k < dt.Rows.Count)
                        {
                            //get name comlum
                            for (int m = 0; m < s.Length; m++)
                            {
                                sb.Append(GetTableName(dt) + "." + s[m].ToString() + " = " + dr[s[m].ToString()].ToString() + " or ");
                            }
                        }
                        if (k == dt.Rows.Count)
                        {
                            //get name comlum
                            for (int m = 0; m < s.Length; m++)
                            {
                                if (k == dt.Rows.Count && i == dstb.Length - 1)
                                {
                                    sb.Append(GetTableName(dt) + "." + s[m].ToString() + " = " + dr[s[m].ToString()].ToString());
                                }
                                else
                                {
                                    sb.Append(GetTableName(dt) + "." + s[m].ToString() + " = " + dr[s[m].ToString()].ToString() + " or ");
                                }

                            }
                        }
                        k++;
                    }
                }

                //su ly remove and cuoi cung 
                int index = sb.ToString().IndexOf("or", sb.ToString().Length - 4);

                if (index != -1)
                {
                    newstring = sb.ToString().Substring(0, index);
                }
                else
                {
                    newstring = sb.ToString();
                }
            }
            catch { return null; }
            return newstring;
        }
    }

}
